package com.fingard.text;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.text.SimpleDateFormat;
import java.util.Date;


/**
 * @author XueJy on 2019/1/28.
 * @version 1.0
 */
public class ExcelHelper {


    /**
     * 读取Excel的数据
     * 多页Excel  ^~^分割sheet页
     *
     */
    public static String readExcel(String filepath) throws Exception {

        Workbook wb = new HSSFWorkbook(new FileInputStream(filepath));
        Sheet sheet = null;
        //获取sheet页数

        int sheetCount = wb.getNumberOfSheets();

        StringBuilder sbFileContent = new StringBuilder();
        for (int x = 0; x < sheetCount; x++) {
            if (x > 0) {
                sbFileContent.append("^~^");
            }
            sheet = wb.getSheetAt(x);
            readSheet(sheet, sbFileContent);

            sbFileContent.append("\r\n");
        }

        return sbFileContent.toString();

    }


    private static void readSheet(Sheet sheet, StringBuilder sbFileContent) {

        for (int i = 3; i < sheet.getLastRowNum(); i++) {
            Row detailRow = sheet.getRow(i);
//            if (detailRow == null) {
//                continue;
//            }
            int cellSize = detailRow.getLastCellNum();
            StringBuilder eachline = new StringBuilder();
            for (int k = 0; k < cellSize; k++) {
                Cell cell = detailRow.getCell(k);
                if (eachline.length() > 0) {
                    eachline.append(",");
                }
                eachline.append(getCellValue(cell));
            }
            sbFileContent.append(eachline);
        }
    }

    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }

        //把数字当成String来读，避免出现1读成1.0的情况
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                Date theDate = cell.getDateCellValue();
                SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                cellValue = dff.format(theDate);
            } else {
                cell.setCellType(Cell.CELL_TYPE_STRING);
            }
        }

        //判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: //数字
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    Date theDate = cell.getDateCellValue();
                    SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cellValue = dff.format(theDate);
                } else {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

}
